# How to use prompting to improve results

:::info Prerequisites

This guide assumes familiarity with the following:

- [Question answering over SQL data](/docs/tutorials/sql_qa)

:::

In this guide we'll go over prompting strategies to improve SQL query generation.
We'll largely focus on methods for getting relevant database-specific information in your prompt.

## Setup

First, install the required packages and set your environment variables. This example will use OpenAI as the LLM.

```bash
npm install @langchain/community @langchain/openai typeorm sqlite3
```

```bash
export OPENAI_API_KEY="your api key"
# Uncomment the below to use LangSmith. Not required.
# export LANGSMITH_API_KEY="your api key"
# export LANGSMITH_TRACING=true

# Reduce tracing latency if you are not in a serverless environment
# export LANGCHAIN_CALLBACKS_BACKGROUND=true
```

The below example will use a SQLite connection with Chinook database. Follow these [installation steps](https://database.guide/2-sample-databases-sqlite/) to create `Chinook.db` in the same directory as this notebook:

- Save [this](https://raw.githubusercontent.com/lerocha/chinook-database/master/ChinookDatabase/DataSources/Chinook_Sqlite.sql) file as `Chinook_Sqlite.sql`
- Run sqlite3 `Chinook.db`
- Run `.read Chinook_Sqlite.sql`
- Test `SELECT * FROM Artist LIMIT 10;`

Now, `Chinhook.db` is in our directory and we can interface with it using the Typeorm-driven `SqlDatabase` class:

import CodeBlock from "@theme/CodeBlock";
import DbCheck from "@examples/use_cases/sql/db_check.ts";

<CodeBlock language="typescript">{DbCheck}</CodeBlock>

## Dialect-specific prompting

One of the simplest things we can do is make our prompt specific to the SQL dialect we're using.
When using the built-in [`createSqlQueryChain`](https://api.js.langchain.com/functions/langchain.chains_sql_db.createSqlQueryChain.html) and [`SqlDatabase`](https://api.js.langchain.com/classes/langchain.sql_db.SqlDatabase.html), this is handled for you for any of the following dialects:

import DialectExample from "@examples/use_cases/sql/prompting/list_dialects.ts";

<CodeBlock language="typescript">{DialectExample}</CodeBlock>

## Table definitions and example rows

In basically any SQL chain, we'll need to feed the model at least part of the database schema.
Without this it won't be able to write valid queries. Our database comes with some convenience methods to give us the relevant context.
Specifically, we can get the table names, their schemas, and a sample of rows from each table:

import TableDefinitionsExample from "@examples/use_cases/sql/prompting/table_definitions.ts";

<CodeBlock language="typescript">{TableDefinitionsExample}</CodeBlock>

## Few-shot examples

Including examples of natural language questions being converted to valid SQL queries against our database in the prompt will often improve model performance, especially for complex queries.

Let's say we have the following examples:

import ExampleList from "@examples/use_cases/sql/prompting/examples.ts";

<CodeBlock language="typescript">{ExampleList}</CodeBlock>

We can create a few-shot prompt with them like so:

import FewShotExample from "@examples/use_cases/sql/prompting/few_shot.ts";

<CodeBlock language="typescript">{FewShotExample}</CodeBlock>

## Dynamic few-shot examples

If we have enough examples, we may want to only include the most relevant ones in the prompt, either because they don't fit in the model's context window or because the long tail of examples distracts the model.
And specifically, given any input we want to include the examples most relevant to that input.

We can do just this using an ExampleSelector. In this case we'll use a [`SemanticSimilarityExampleSelector`](https://api.js.langchain.com/classes/langchain_core.example_selectors.SemanticSimilarityExampleSelector.html),
which will store the examples in the vector database of our choosing.
At runtime it will perform a similarity search between the input and our examples, and return the most semantically similar ones:

import DynamicFewShotExample from "@examples/use_cases/sql/prompting/dynamic_few_shot.ts";

<CodeBlock language="typescript">{DynamicFewShotExample}</CodeBlock>

## Next steps

You've now learned about some prompting strategies to improve SQL generation.

Next, check out some of the other guides in this section, like [how to query over large databases](/docs/how_to/sql_large_db).
